﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;

namespace SqlServerDataTableToExcel.DBhelper
{
    public abstract class SQLiteHelper
    {
  
        /// <summary>
        /// 创建一个SQLITE的数据库
        /// </summary>
        /// <param name="DataSource">数据库完整路径名字</param>
        public static void createNewDatabase(string DataSource)
        {
            SQLiteConnection.CreateFile(DataSource);
        }



        //在指定数据库中创建一个table
        public static void createTable(string DataSource, string SQL)
        {
            SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + DataSource + ";Version=3;");
            try
            {
                m_dbConnection.Open();
                SQLiteCommand command = new SQLiteCommand(SQL, m_dbConnection);
                command.ExecuteNonQuery();

            }
            catch (Exception ex)
            { throw ex; }
            finally
            {
                if (m_dbConnection.State == System.Data.ConnectionState.Open)
                {
                    m_dbConnection.Close();
                }
                m_dbConnection.Dispose();
            }
        }


        public static void ExecuteSQL(string DataSource, List<string> SQLlist)
        {
            SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + DataSource + ";Version=3;");
            try
            {
                m_dbConnection.Open();
                foreach (string SQL in SQLlist)
                {
                    SQLiteCommand command = new SQLiteCommand(SQL, m_dbConnection);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            { throw ex; }
            finally
            {
                if (m_dbConnection.State == System.Data.ConnectionState.Open)
                {
                    m_dbConnection.Close();
                }
                m_dbConnection.Dispose();
            }


        }

        //使用sql查询语句，并显示结果
        public static DataTable Query(string DataSource, string SQL)
        {

            SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + DataSource + ";Version=3;");
            try
            {
                SQLiteDataAdapter reader = new SQLiteDataAdapter();
               SQLiteCommand command = new SQLiteCommand(SQL, m_dbConnection);
               reader.SelectCommand = command;
                DataTable dt = new DataTable();
                reader.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (m_dbConnection.State == System.Data.ConnectionState.Open)
                {
                    m_dbConnection.Close();
                }
                m_dbConnection.Dispose();
            }
        }


        public static SQLite GetTrans(string DataSource)
        {
            SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + DataSource + ";Version=3;");
            m_dbConnection.Open();
            SQLiteTransaction sqlTranns = m_dbConnection.BeginTransaction();
            SQLite rtn = new SQLite();
            rtn.sqlconn = m_dbConnection;
            rtn.sqltrann = sqlTranns;
            return rtn;
        }


        public static DataTable GetTransDataTable(SQLite tranInfo,string SQL)
        {

            SQLiteDataAdapter reader = new SQLiteDataAdapter();
            SQLiteCommand command = new SQLiteCommand(SQL);
            command.Transaction = tranInfo.sqltrann;
            reader.SelectCommand = command;
            DataTable dt = new DataTable();
            reader.Fill(dt);
            return dt;
        }

        public static void GetTransUpdate(SQLite tranInfo, string SQL)
        {
            SQLiteCommand command = new SQLiteCommand(SQL);
            command.Transaction = tranInfo.sqltrann;
            command.ExecuteNonQuery();
        }

        public static void GetTransCommit(SQLite tranInfo)
        {
            tranInfo.sqltrann.Commit();
        }
        public static void GetTransRollback(SQLite tranInfo)
        {
            tranInfo.sqltrann.Rollback();
        }

    }

    public class SQLite
    {
        public SQLiteConnection sqlconn { get; set; }
        public SQLiteTransaction sqltrann { get; set; }
    }
}


